<?php
namespace App\Repositories\GameServer;

use App\Models\GameServer\LoginRecord;
use Carbon\Carbon;
use DB;

/**
* 玩家登录日志
*/
class LoginRecordRepository
{
    //获取某玩家登录日志
    public function getLoginRecords($id)
    {
        $login_records = LoginRecord::where('account_id', $id)->orderBy('login_time', 'desc')->get();

        // dd($login_records);
        return $login_records;
    }

    //统计某玩家登录次数
    public function getLoginRecordsCount($id)
    {
        return LoginRecord::where('account_id', $id)->count();
    }

    //月活跃用户数量(当月有一次登录游戏)
    public static function getMonthActiveNum()
    {
        $month = Carbon::now()->startOfMonth();

        $count = LoginRecord::select('account_id')
                            ->distinct()
                            ->where('login_time', '>=', $month->timestamp)
                            ->get()
                            ->count();

        return $count;
    }

    //当月活跃用户id
    public static function getMonthActiveGamerIds()
    {
        $month = Carbon::now()->startOfMonth();

        //月活跃用户id
        $gamer_ids = LoginRecord::where('login_time', '>=', $month->timestamp)
                                ->groupBy('account_id')
                                ->pluck('account_id')
                                ->all();

        return $gamer_ids;
    }

    //当日用户平均在线时长=当日所有用户的在线总和/有效用户数
    public static function getAvgOnlineTime()
    {
        $start = Carbon::now()->startOfDay()->timestamp;
        $end = Carbon::now()->endOfDay()->timestamp;

        //当日所有用户的在线总和
        $avg_time = LoginRecord::select('id', DB::raw('(logout_time - login_time) as diff_time1'))
                                ->where('login_time', '>=', $start)
                                ->where('logout_time', '<=', $end)
                                ->whereColumn('logout_time', '>', 'login_time')
                                ->get()
                                ->sum('diff_time1');
        dd($avg_time);

        //需求已更改,删除此功能
    }

    /**
     * [获取活跃用户 == 有一次登录]
     * select h, count(*) from
            (select distinct account_id, from_unixtime(login_time,'%k') as h from t_login_record where login_time >= unix_timestamp('2017-11-13 00:00:00') and login_time < unix_timestamp('2017-11-13 23:59:59'))tmp
        group by h;
     * @param  [type] $where [description]
     * @return [type]        [description]
     */
    public static function getActiveGamers($where)
    {
        $sub = LoginRecord::select('account_id', DB::raw('FROM_UNIXTIME(login_time,"'.$where['format'].'") as time'))
                        ->whereBetween('login_time', [$where['start'], $where['end']])
                        ->distinct();

        $gamers = DB::connection('game_server')
                    ->table(DB::raw("({$sub->toSql()}) as sub"))
                    ->mergeBindings($sub->getQuery())// 绑定参数，否则sql语句会只有'?'
                    ->select('time', DB::raw('COUNT(*) as num'))
                    ->groupBy('time')
                    ->orderBy('time', 'asc')
                    ->get();

        // pd($gamers);
        return $gamers;

    }

    //获取活跃用户 -- 渠道时间分组
    public function getActiveGamerOfChnnel($where)
    {
        $channel = $where['channel'];

        $gamers = LoginRecord::select('distributer_id', DB::raw('FROM_UNIXTIME(login_time,"%Y-%m-%d") as date, COUNT(distinct(account_id)) as count'))
                            ->when($channel, function($query) use ($channel) {
                                return $query->whereIn('distributer_id', $channel);
                            })
                            ->whereBetween('login_time', [$where['start'], $where['end']])
                            ->groupBy('date', 'distributer_id')
                            ->orderBy('date', 'asc')
                            ->get();
        return $gamers;
    }

    //获取某段时间某一特定渠道的用户 -- 用于计算留存率
    public function getLogin($where)
    {
        $gamers = LoginRecord::select('account_id')
                            ->whereBetween('login_time', [$where['start'], $where['end']])
                            ->where('distributer_id', $where['channel'])
                            ->whereIn('account_id', $where['ids'])
                            ->groupBy('account_id')
                            ->get();
        return $gamers;
    }
}